import pandas as pd
import matplotlib.pyplot as plt
import os
from datetime import datetime

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
plt.rcParams['axes.unicode_minus'] = False

# 1. 读取Excel文件
try:
    # 自动检测文件编码和表头
    df = pd.read_excel('FhjlViewDD.xlsx', engine='openpyxl')
    
    # 2. 数据清洗和预处理
    # 确保日期列格式正确
    df['日期'] = pd.to_datetime(df['日期'], errors='coerce')
    # 删除无效日期行
    df = df.dropna(subset=['日期'])
    
    # 3. 筛选6月份数据
df_june = df[(df['日期'].dt.month == 6) & (df['日期'].dt.year == datetime.now().year)]

# a. 矿粉货运量日趋势分析
def plot_mineral_daily():
    plt.figure(figsize=(14, 7))
    mineral = df_june.groupby(df_june['日期'].dt.day)['矿粉货运量'].sum()
    ax = mineral.plot(kind='bar', color='#1f77b4', width=0.8)
    
    # 添加数据标签
    for p in ax.patches:
        ax.annotate(f"{p.get_height():.1f}", 
                   (p.get_x() + p.get_width() / 2., p.get_height()),
                   ha='center', va='center', 
                   xytext=(0, 5), 
                   textcoords='offset points')
    
    plt.title(f'{datetime.now().year}年6月矿粉货运量日趋势', fontsize=16, pad=20)
    plt.xlabel('日期(日)', fontsize=12)
    plt.ylabel('货运量(吨)', fontsize=12)
    plt.xticks(rotation=0)
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('矿粉货运量日趋势.png', dpi=300, bbox_inches='tight')
    plt.close()
    return mineral

# b. 水泥货运量日趋势分析
def plot_cement_daily():
    plt.figure(figsize=(14, 7))
    cement = df_june.groupby(df_june['日期'].dt.day)['水泥货运量'].sum()
    ax = cement.plot(kind='bar', color='#ff7f0e', width=0.8)
    
    # 添加数据标签
    for p in ax.patches:
        ax.annotate(f"{p.get_height():.1f}", 
                   (p.get_x() + p.get_width() / 2., p.get_height()),
                   ha='center', va='center', 
                   xytext=(0, 5), 
                   textcoords='offset points')
    
    plt.title(f'{datetime.now().year}年6月水泥货运量日趋势', fontsize=16, pad=20)
    plt.xlabel('日期(日)', fontsize=12)
    plt.ylabel('货运量(吨)', fontsize=12)
    plt.xticks(rotation=0)
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.savefig('水泥货运量日趋势.png', dpi=300, bbox_inches='tight')
    plt.close()
    return cement

# c. 客户需求分析
def analyze_client_demand():
    client = df_june.groupby('客户名称')['货运量'].sum().sort_values(ascending=False)
    client.to_csv('客户需求排序.csv', encoding='utf-8-sig')
    return client

# d. 发货地分析
def analyze_location_distribution():
    plt.figure(figsize=(10, 10))
    location = df_june.groupby('发货地')['货运量'].sum()
    
    # 只显示前8大发货地，其余归为"其他"
    if len(location) > 8:
        other = location[8:].sum()
        location = location[:8]
        location['其他'] = other
    
    explode = [0.1 if i == location.idxmax() else 0 for i in location.index]
    
    wedges, texts, autotexts = plt.pie(location, 
                                      autopct='%1.1f%%',
                                      pctdistance=0.85,
                                      explode=explode,
                                      startangle=90,
                                      textprops={'fontsize': 12})
    
    plt.title('6月各发货地发货量占比', fontsize=16, pad=20)
    plt.legend(wedges, location.index,
              title="发货地",
              loc="center left",
              bbox_to_anchor=(1, 0, 0.5, 1))
    plt.savefig('发货地总量.png', dpi=300, bbox_inches='tight')
    plt.close()
    return location

# e. 车牌号分析
def analyze_plate_numbers():
    plate = df_june.groupby('车牌号')['货运量'].sum().sort_values(ascending=False)
    plate.to_csv('车牌号货运量排序.csv', encoding='utf-8-sig')
    return plate

# f. 生成分析报告
def generate_report(mineral, cement, client, location, plate):
    report = f'''# A公司{datetime.now().year}年6月货运情况分析

## 1. 矿粉货运量日趋势

![矿粉货运量](矿粉货运量日趋势.png)

- 最高货运量: {mineral.max():.1f}吨 (第{mineral.idxmax()}日)
- 最低货运量: {mineral.min():.1f}吨 (第{mineral.idxmin()}日)
- 平均日货运量: {mineral.mean():.1f}吨
- 总货运量: {mineral.sum():.1f}吨

## 2. 水泥货运量日趋势

![水泥货运量](水泥货运量日趋势.png)

- 最高货运量: {cement.max():.1f}吨 (第{cement.idxmax()}日)
- 最低货运量: {cement.min():.1f}吨 (第{cement.idxmin()}日)
- 平均日货运量: {cement.mean():.1f}吨
- 总货运量: {cement.sum():.1f}吨

## 3. 客户需求排名(前10名)